Importing all the necessary libraries

Loading Datasets into the DataFrames

Finding Size of the DataFrame

Visualizing data head

Displaying head of the columns

EXPLORATORY DATA ANALYSIS

AIRPORT_CODES data

Flights Data

fixing data type for dates

There is data duplicacy so dropping the 4410 duplicates

TICKETS data

MERGING DATASETS

To optimize merging, I am first aggregating the ticketsand flights data by origin and destination, summarizing key metrics (e.g., mean ticket price, total flights). This reduces data size and complexity. Afterward, I'll merge these aggregated datasets with airport_codes, streamlining computations and improving efficiency.

Advantages of This Approach: Scalability: This method is well-suited for larger datasets, as it reduces memory overhead.

Readability: Aggregating at the source level simplifies subsequent analysis and avoids complex joins on larger datasets.

Flexibility: You can easily add or adjust the aggregate metrics without reworking the entire process.

ANALYSIS

1: The 10 busiest round trip routes in terms of number of round trip flights in the quarter.

THE FLIGHTS DATA SET IS ALREADY FILTERED TO REMOVE THE CANCELLED FLIGHTS

Average Occupancy Rate

NOTE - In analyzing the top 10 occupied round trip routes, each route is considered distinct regardless of the order of origin and destination.

2 - The 10 most profitable round trip routes (without considering the upfront airplane cost) in the quarter. Along with the profit, show total revenue, total cost, summary values of other key components and total round trip flights in the quarter for the top 10 most profitable routes.

Total Profit generated by a route in a quarter.

Total Profit generated by a route in 1 round trip

NOTES

To gain a clearer understanding of our market strategy and revenue potential, we need to refine our analysis. While identifying the busiest round trip routes provides a vivid snapshot, it's crucial to align our focus with profitable opportunities.

Let’s strengthen our analysis by classifying airports based on their level of operational activity. This involves identifying airports that qualify as 'busy' to focus our efforts on maximizing customer engagement and driving revenue growth.

It is also crucial to take the route into consideration, so that it helps the analysis, to get a clear understanding on which routes are more occupied and have likelihood of exceeding the threashold for profitability consideration.

Operating a fleet of 5 aircraft presents a fantastic opportunity to establish a highly efficient and flexible aviation operation, whether you're focused on passenger transportation, cargo delivery, or charter services.

Airports with a number of flights exceeding 600 are considered having busy routes, indicating significant operational activity.

This approach integrates operational capacity, profitability considerations, and seasonal adjustments to effectively identify airports with substantial flight activity, aligning with strategic decision-making in aviation operations.

Total Profit generated by a route in 1 round trip in busy routes

Top 10 Least Delay in busy routes

Strategic Goals:

To achieve company goals, we aim to maximize profitability through strategic initiatives, expand our customer base, and maintain operational excellence with a focus on punctuality. A unified scoring system will evaluate performance, with metrics for profitability, total flights, and delays. The scores will range from 0 to 1, with a weighted average (Delay 40%, Total Flights 30%, Profitability 30%) to assess overall operational effectiveness and alignment with strategic objectives.

3: The 5 round trip routes that you recommend to invest in based on any factors that you choose.

Recommended Routes:

New York, NY to Las Vegas, NV New York, NY to Los Angeles, CA Minneapolis, MN to Orlando, FL Los Angeles, CA to New York, NY New York, NY to San Francisco, CA

Rationale:

These routes are recommended based on a comprehensive weighted average analysis that includes KPI's such as the total number of flights (total_count_scaled), profitability per trip (profit_per_trip_scaled), and delay times (delay_scaled).

By analyzing these metrics collectively, the company can adopt a well-rounded strategy that enhances both profitability and operational efficiency. Focusing on routes with strong performance across these metrics will help the company uphold its reputation for punctuality while driving financial success.

4: The number of round trip flights it will take to breakeven on the upfront airplane cost for each of the 5 round trip routes that you recommend. Print key summary components for these routes.

5: Key Performance Indicators (KPI’s) that you recommend tracking in the future to measure the success of the round trip routes that you recommend.

Essential KPI's which are already given to us are

Distance Fare Arrival Delay Departure Delay Occupancy Rate

Additional Key Performance Indicators (KPIs) for Airline Analysis:

Demographic insights optimize marketing and service customization.

Running an analysis on the pricing of Economy, Basic Economy, and Business fares can help identify optimal ticket pricing strategies. By adjusting fares based on passenger demand and competition, we can attract more passengers while maximizing revenue across different fare classes.

Evaluating additional services such as meals, luxury products, drinks, travel perks, and discounts can enhance customer satisfaction and drive revenue. Tracking their adoption, costs, and impact on repeat business helps optimize profitability and loyalty.

Weather analysis enhances schedule reliability and passenger satisfaction.

Government Policies supporting tourism and business drive route expansion opportunities.

Tracking overall onboarding experience to can be one of the key parameter for customer satisfaction and customer to return for same flight experience for travelling.

These KPIs enable comprehensive analysis, supporting strategic decisions to optimize operations, revenue, and customer satisfaction in the airline industry.